


/*dbcc checkident(Sales_Order, reseed, 1)
dbcc checkident(Customer_Open_Order_LineItem, reseed, 1)
dbcc checkident(Customer_Change_Order_LineItem, reseed, 1)
dbcc checkident(Customer_Shipping_Table, reseed, 1)
dbcc checkident(Customer_Shipping_Line_Item_Table, reseed, 1)
*/

ALTER    proc sp_so_order_to_shipped_order
@sales_order_number varchar(50)
as
begin
	--select *from Customer_Shipping_line_item_table
	--select *from Customer_Shipping_Table
	--select *from Sales_order where SalesOrder_No = '10164'
	declare @so_id bigint 
	declare @line_item_id bigint
	declare @ps_lable varchar(50)
	declare @customer_shipping_id int
	declare @cool_qty int
	declare @cool_sch_date varchar(30)
	declare @cslit_id int

	select @so_id =  SalesOrder_ID from Sales_Order where SalesOrder_No = @sales_order_number

	--select *from Customer_Open_Order_LineItem where SalesOrder_ID = 1063
	--select *from Sales_Hold_Table where LineItem_ID = 2
	
	update Sales_Hold_Table set Hold_Reason_Code = '', Is_Approved = 0 where SalesOrder_ID = @so_id
	update Sales_Order set SO_Status = 'APPROVED', SO_Is_Pending = 1, SO_Is_Shipped = 0 where SalesOrder_ID =  @so_id
	declare line_item_cursor CURSOR for
	select LineItem_ID from Customer_Open_Order_LineItem where SalesOrder_ID = @so_id

	open line_item_cursor
		fetch next from line_item_cursor into @line_item_id
		while @@fetch_status = 0
		begin
			set @ps_lable = 'PS-'+@sales_order_number+'-'+convert(varchar, @line_item_id)
			select @cool_qty = COOL_Qty, @cool_sch_date = COOL_Schedule_Date from Customer_Open_Order_LineItem where LineItem_ID = @line_item_id
			update Customer_Open_Order_LineItem set COOL_Status = 'SHIPPED' where LineItem_ID = @line_item_id
				
		
			-- update Customer_Open_Order_LineItem set COOL_Schedule_Date='01/03/2003' where LineItem_ID = 35
			insert Customer_Shipping_Table(Label, SalesOrder_ID, CST_Status) values(@ps_lable, /*'PS-10164'*/ @so_id, /*1063*/ 'SHIPPED')
			--select *from Customer_Shipping_Table where Label = 'PS-10164'
			set @customer_shipping_id = @@IDENTITY
			update Customer_Shipping_Table set CST_Ship_Date = @cool_sch_date/*'09/12/2002'*/, Timestamp = CURRENT_TIMESTAMP where CustomerShipping_ID = @customer_shipping_id
			insert Customer_Shipping_line_item_table(CustomerShipping_ID, LineItem_ID, CSLIT_Qty_Shipped, CSLIT_Shipped_Date, CSLIT_Status) values(@customer_shipping_id /*26*/, @line_item_id/*2*/, @cool_qty/*1*/, @cool_sch_date/*'09/12/2002'*/, 'SHIPPED')
			set @cslit_id = @@IDENTITY
			insert Customer_Shipping_Lot_Number_Table(CSLIT_ID)values(@cslit_id)
			update Customer_Open_Order_LineItem set COOL_Status = 'SHIPPED' where LineItem_ID = @line_item_id
			
			--select *from Customer_Shipping_Table
			/*print 'Sales Order Number = '+ @sales_order_number
			print 'SalesOrder_ID = '+convert(varchar, @so_id)
			print 'LineItem_ID = '+convert(varchar, @line_item_id)
			print 'COOL_Qty = '+convert(varchar, @cool_qty)
			print 'COOL_Schedule_Date = '+ @cool_sch_date
			*/
			set @line_item_id = null
			set @cool_qty = null
			set @cool_sch_date = null

			fetch next from line_item_cursor into @line_item_id
		end
	close line_item_cursor
	deallocate line_item_cursor

end

--exec sp_so_order_to_shipped_order '10223'




